<?php 
	class WS_DepartmentSubjectCourseMod {
		
		private $_DB;
		
		public function __construct() {

			$this->_DB 	= new ConfigDB();

		}
		
		#### DEPARTMENT
			#DEPARTMENT LISTING
			public function getListDepartment($strID) {
				
				if (!empty($strID)) {
					$strID = escapestring($strID);
					$whereID = "`dept_idPK` = '" . $strID . "' AND ";
				}
				
				return $this->_DB->selectDB("SELECT `dept_idPK`, `dept_desc`, `dept_code` FROM `tbl_department` WHERE " . $whereID . "`is_deleted` = 'N'");
	
			}
			#SECTION LISTING
			public function getListSection() {
				
				return $this->_DB->selectDB("SELECT `section_idPK`, `section_desc`, `course_idFK`, `no_stud`, `sem_idFK` FROM `tbl_section` WHERE `is_deleted` = 'N'");
	
			}
			
			public function getProfListSection($strSubjecList) {
				
				return $this->_DB->selectDB("SELECT `section_idPK`, `section_desc`, `course_idFK`, `no_stud`, `sem_idFK` FROM `tbl_section` WHERE `is_deleted` = 'N' AND `sem_idFK` IN (SELECT DISTINCT `sem_linker_idFK` FROM `tbl_subject` WHERE `subj_idPK` IN (" . $strSubjecList . "))");
	
			}
			
			
			#SEMESTER LISTING
			public function getListSemesters() {
				
				return $this->_DB->selectDB("SELECT sl.`sem_linker_idPK`, s.`sem_desc` FROM `tbl_sem_linker` sl INNER JOIN `tbl_semesters` s ON sl.`sem_idFK` = s.`sem_idPK` AND sl.`is_deleted` = 'N'");
	
			}
	
			#DEPARTMENT via ID
			public function getDepartment($strID) {
	
				$strID = escapestring($strID);
	
				return $this->_DB->selectDB2("SELECT `dept_idPK`, `dept_desc`, `dept_code` FROM `tbl_department` WHERE `dept_idPK` = '" . $strID . "' AND `is_deleted` = 'N'");
	
			}
			
		##### COURSES
			#LIST OF COURSES
			public function getListCourses($strID) {
				
				if (!empty($strID)) {
					$strID = escapestring($strID);
					$whereID = "`dept_idFK` = '" . $strID . "' AND ";
				}
				
				return $this->_DB->selectDB("SELECT `course_idPK`, `course_desc`, `course_code`, `course_major`, `is_deleted` FROM `tbl_courses` WHERE " . $whereID . "`is_deleted` = 'N' ORDER BY `course_desc` ASC");
	
			}
	
			#GET COURSE
			public function getCourse($strID) {
				
				$strID = escapestring($strID);
				
				return $this->_DB->selectDB2("SELECT `course_idPK`,
													 `course_desc`,
													 `course_major`,
													 `course_code`
											  FROM 	 `tbl_courses`
											  WHERE  `course_idPK` = '" . $strID . "'
											  LIMIT 1");
	
			}
			
			#INSERT COURSE
			public function insertCourse($strArray) {
	
				$course	= escapestring($strArray['course']);
				$major 	= escapestring($strArray['major']);
				$code 	= escapestring($strArray['code']);
				$deptID	= escapestring($strArray['dept_id']);
	
				$this->_DB->queryDB("INSERT INTO `tbl_courses` (`course_desc`, `course_major`, `course_code`, `dept_idFK`) VALUES ('" . $course  . "', '" . $major  . "', '" . $code  . "', '" . $deptID . "')");
				
			}
			
			#UPDATE COURSE
			public function updateCourse($strArray, $strID) {
	
				$course	= escapestring($strArray['course']);
				$major 	= escapestring($strArray['major']);
				$code 	= escapestring($strArray['code']);
				$strID	= escapestring($strID);
	
				$this->_DB->queryDB("UPDATE `tbl_courses` SET `course_desc` = '" . $course  . "', `course_major` = '" . $major  . "', `course_code` = '" . $code  . "' WHERE `course_idPK` = '" . $strID . "'");
				
			}
			
			#DELETE COURSE
			public function deleteCourse($strID) {
	
				$strID	= escapestring($strID);
	
				$this->_DB->queryDB("UPDATE `tbl_courses` SET `is_deleted` = 'Y' WHERE `course_idPK` = '" . $strID . "'");
				
			}
			
		##### CHECKLIST
			#CHECKLIST HEADER PRINTING
			public function getChecklistHeader($strID) {
				
				$strID 		= escapestring($strID);
				
				return $this->_DB->selectDB2("SELECT h.`head_idPK`,
													 CONCAT(y.`year_from`, '-', y.`year_to`) as `head_year`,
													 h.`head_based`
											  FROM `tbl_header` h
											  LEFT JOIN `tbl_year` y ON y.`year_idPK` = h.`year_idFK`
											  WHERE h.`dept_idFK` = '" . $strID . "' AND h.`is_deleted` = 'N' LIMIT 1");
	
			}
	
			#INSERT CHECKLIST HEADER
			public function insertCheckListHeader($strHeadYear, $strHeadBased, $strDeptID) {
					
					$headYear 	= escapestring($strHeadYear);
					$headBased 	= escapestring($strHeadBased);
					$deptID 	= escapestring($strDeptID);
					
					$this->_DB->queryDB("INSERT INTO `tbl_header` (`year_idFK`, `head_based`, `dept_idFK`) VALUES ('" .$headYear  . "', '" .$headBased  . "', '" . $deptID . "')");
					
					return mysql_insert_id();
			}
	
			#UPDATE CHECKLIST HEADER
			public function updateCheckListHeader($strDeptID) {
					
					$strDeptID 	= escapestring($strDeptID);
					$this->_DB->queryDB("UPDATE `tbl_header` SET `is_deleted` = 'Y' WHERE `dept_idFK` = '" . $strDeptID . "'");
	
			}
			
		##### SEMESTER LINKER
		
			public function getNextSemesterIsActive($strSemesterID, $strCourseID) {
				
				$strSemesterID 	= escapestring($strSemesterID);
				$strCourseID 	= escapestring($strCourseID);
				
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count` FROM `tbl_sem_linker` WHERE `course_idFK` = '" . $strCourseID . "' AND `sem_idFK` = '" . $strSemesterID . "' AND `is_deleted` = 'N'");
			}
		
			public function getSemesterIrregular($strCourseID, $strStudID) {
				
				$strCourseID = escapestring($strCourseID);
				$strStudID   = escapestring($strStudID);
				
				return $this->_DB->selectDB("SELECT DISTINCT tssl.`sem_idFK` FROM `tbl_stud_subj_linker` tssl WHERE tssl.`course_idFK` = '" . $strCourseID . "' AND tssl.`stud_idFK` = '" . $strStudID . "'");
			}
			
			public function getSemesterIrregularLinkerDesc($strSemIDs) {
				
				$semLInkerIDs = "";
					foreach($strSemIDs as $strSemIDsValue) {
						$semLInkerIDs .= "'" . escapestring($strSemIDsValue) . "',";
					}
					$semLInkerIDsTrim = trim(rtrim($semLInkerIDs, ','));
					
				return $this->_DB->selectDB("SELECT	  sl.`sem_linker_idPK`,
													  s.`sem_desc`
													FROM `tbl_sem_linker` sl
													 INNER JOIN `tbl_semesters` s
														ON (sl.`sem_idFK` = s.`sem_idPK`)
													 INNER JOIN `tbl_year` `y`
														ON (`y`.`year_idPK` = sl.`year_idFK`)
													WHERE sl.sem_linker_idPK IN (" . $semLInkerIDsTrim . ")");
			}
		
			public function getSemesterLinker($strCourseID, $strSemesterActive, $strIsDeleted) {
				
				$strCourseID = escapestring($strCourseID);
				
				$whereSemesterActive = "";
				if (!empty($strSemesterActive)) {
					$whereSemesterActive .= " AND `sem_linker_idPK` NOT IN (" . $strSemesterActive . ")";
				}
				
				if ( $strIsDeleted === true ) {
					$whereSemesterActive .= " AND sl.`is_deleted` = 'N'";
				}
				
				return $this->_DB->selectDB("SELECT 	sl.`sem_linker_idPK`, sl.`is_deleted`, sl.`sem_idFK`, sl.`note`, s.`sem_desc`, sl.`course_idFK`, c.`course_desc`, c.`course_code`, CONCAT(y.`year_from`, '-', y.`year_to`) as `head_year`, h.`head_based`
											 FROM 		`tbl_sem_linker` sl
												INNER JOIN `tbl_semesters` s 	ON (sl.`sem_idFK` 	 = s.`sem_idPK`)
												INNER JOIN `tbl_courses` c 		ON (sl.`course_idFK` = c.`course_idPK`)
												INNER JOIN `tbl_header` h 		ON (sl.`head_idFK` 	 = h.`head_idPK`)
												INNER JOIN `tbl_year` y 		ON (y.`year_idPK` 	 = sl.`year_idFK`)
											 WHERE 		c.`course_idPK` = '" . $strCourseID . "'" . $whereSemesterActive . " ORDER BY s.`sem_code`");
			}
		
			public function getSemesterLinkerEnrolled($strCourseID, $strStudID) {
				
				$strCourseID = escapestring($strCourseID);
				$strStudID = escapestring($strStudID);
				
				return $this->_DB->selectDB("SELECT DISTINCT sl.`sem_linker_idPK`, sl.`sem_idFK`, sl.`note`, s.`sem_desc`, sl.`course_idFK`, c.`course_desc`, c.`course_code`, CONCAT(y.`year_from`, '-', y.`year_to`) as `head_year`, h.`head_based`
											 FROM 		`tbl_stud_subj_linker` tssl
												INNER JOIN `tbl_sem_linker` sl 	ON (tssl.`sem_idFK` = sl.`sem_idFK`)
												INNER JOIN `tbl_semesters` s 	ON (sl.`sem_idFK` 	 = s.`sem_idPK`)
												INNER JOIN `tbl_courses` c 		ON (sl.`course_idFK` = c.`course_idPK`)
												INNER JOIN `tbl_header` h 		ON (sl.`head_idFK` 	 = h.`head_idPK`)
												INNER JOIN `tbl_year` y 		ON (y.`year_idPK` 	 = sl.`year_idFK`)
											 WHERE 		c.`course_idPK` = '" . $strCourseID . "' AND tssl.`stud_idFK` = '" . $strStudID . "' AND sl.`is_deleted` = 'N' AND y.`is_deleted` = 'N' ORDER BY s.`sem_code`");
			}
		
			public function getSemesterLinkerKeyArrangement($strCourseID, $nextSemester, $isMultiple) {
				
				$strCourseID  = escapestring($strCourseID);
				$nextSemester = escapestring($nextSemester);
				
				if (!empty($nextSemester)) {
					if ( $isMultiple > 1 ) {
						$whereNextSemester = "AND sl.`sem_linker_idPK` NOT IN (" . $nextSemester . ")";
					}
					else {
						$whereNextSemester = "AND sl.`sem_linker_idPK` > '" . $nextSemester . "'";
					}
				}
				
				return $this->_DB->selectDB2("SELECT DISTINCT sl.`sem_linker_idPK`,
															  sl.`sem_idFK`,
															  s.`key_arrange`,
															  s.`sem_year`
													FROM 	 `tbl_sem_linker` sl
													INNER JOIN `tbl_semesters` s
														ON s.`sem_idPK` = sl.`sem_idFK`
													WHERE sl.`course_idFK` = '" . $strCourseID . "'
														" . $whereNextSemester . "
														AND sl.`is_deleted` = 'N'
													ORDER BY s.`key_arrange` ASC LIMIT 1");
			}
		
			public function getSemesterLinkerPreviousSemester($strCourseID, $nextSemester) {
				
				$strCourseID  = escapestring($strCourseID);
				$nextSemester = escapestring($nextSemester);
				
				return $this->_DB->selectDB("SELECT DISTINCT sl.`sem_linker_idPK`,
															  sl.`sem_idFK`,
															  s.`key_arrange`,
															  s.`sem_year`
													FROM 	 `tbl_sem_linker` sl
													INNER JOIN `tbl_semesters` s
														ON s.`sem_idPK` = sl.`sem_idFK`
													WHERE sl.`course_idFK` = '" . $strCourseID . "'
														AND sl.`sem_linker_idPK` < '" . $nextSemester . "'
														AND sl.`is_deleted` = 'N'");
			}
		
			public function getCountSubjectAll($semesterLinker) {
				
				$semesterLinker = escapestring($semesterLinker);
				
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count`, s.`sem_linker_idFK`
											 FROM `tbl_subject` s
												WHERE s.`sem_linker_idFK` = '" . $semesterLinker . "'
													AND s.`is_deleted` = 'N'");
			}
		
			public function getCountSubjectTaken($strStudID, $strCourseID, $semesterLinker) {
				
				$strStudID  = escapestring($strStudID);
				$strCourseID  = escapestring($strCourseID);
				$semesterLinker = escapestring($semesterLinker);
				
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count`, `sem_idFK`
											 FROM `tbl_stud_subj_linker`
												WHERE `sem_idFK` = '" . $semesterLinker . "'
													AND `course_idFK` = '" . $strCourseID . "'
													AND `stud_idFK` = '" . $strStudID . "'
													AND `is_deleted` = 'N'");
			}
		
			public function getSemesterLinkerKeyArrangementLastSemester($strCourseID, $nextSemester) {
				
				$strCourseID  = escapestring($strCourseID);
				$nextSemester = escapestring($nextSemester);
				
				return $this->_DB->selectDB2("SELECT DISTINCT sl.`sem_linker_idPK`,
															  sl.`sem_idFK`,
															  s.`key_arrange`,
															  s.`sem_year`
													FROM 	 `tbl_sem_linker` sl
													INNER JOIN `tbl_semesters` s
														ON s.`sem_idPK` = sl.`sem_idFK`
													WHERE sl.`course_idFK` = '" . $strCourseID . "'
														AND sl.`is_deleted` = 'N'
													ORDER BY s.`key_arrange` DESC LIMIT 1");
			}
		
			public function getFreshmenSemesterLinker($strCourseID) {
				
				$strCourseID = escapestring($strCourseID);
				
				return $this->_DB->selectDB2("SELECT sl.`sem_linker_idPK`, s.`sem_desc`
												FROM `tbl_sem_linker` sl
												INNER JOIN `tbl_semesters` s 	ON (sl.`sem_idFK` 	 = s.`sem_idPK`)
												INNER JOIN `tbl_year` y ON (y.`year_idPK` 	 = sl.`year_idFK`)
												WHERE sl.`course_idFK` = '" . $strCourseID . "' AND s.`sem_code` = '1/1SEM' AND sl.`is_deleted` = 'N'");
			}
			
			public function getSemesterLinkerNote($strSem, $strCourse, $strYear) {
				
				$strSem 	= escapestring($strSem);
				$strCourse 	= escapestring($strCourse);
				$strYear 	= escapestring($strYear);
				
				return $this->_DB->selectDB2("SELECT `sem_linker_idPK`, `note`, `is_deleted`
											  FROM `tbl_sem_linker`
											  WHERE `sem_idFK` = '" . $strSem . "' AND `course_idFK` = '" . $strCourse . "' ORDER BY `sem_linker_idPK` ASC LIMIT 1");
											  
			}
			
			public function getTotalUnitsPerSemesterLinker($strSemLinkerID) {
				
				$strSemLinkerID = escapestring($strSemLinkerID);
				
				return $this->_DB->selectDB("SELECT ABS(`total_units`) AS `abs_total_units` FROM `tbl_subject` WHERE `sem_linker_idFK` = '" . $strSemLinkerID . "' AND is_deleted = 'N'");
											  
			}
			
			public function getTotalUnitsPerSemesterLinkerActive($strSemLinkerID, $strStudID) {
				
				$strSemLinkerID = escapestring($strSemLinkerID);
				$strStudID 		= escapestring($strStudID);
				
				return $this->_DB->selectDB("SELECT ABS(s.`total_units`) AS `abs_total_units` FROM `tbl_stud_subj_linker` tssl INNER JOIN `tbl_subject` s ON s.subj_idPK = tssl.subj_idFK WHERE tssl.`stud_sem_active` = '" . $strSemLinkerID . "' AND tssl.`stud_idFK` = '" . $strStudID . "' AND tssl.`is_deleted` = 'N'");
											  
			}
			
			public function insertSemesterLinker($strSem, $strCourse, $strHead, $strYear) {
					
					$sem_id 	= escapestring($strSem);
					$course_id	= escapestring($strCourse);
					$head_id 	= escapestring($strHead);
					$year_id 	= escapestring($strYear);
					
					$this->_DB->queryDB("INSERT `tbl_sem_linker` (`sem_idFK`, `course_idFK`, `head_idFK`, `year_idFK`) VALUES ('" . $sem_id . "', '" . $course_id . "', '" . $head_id . "', '" . $year_id . "')");
	
			}
			
			public function udpateSaveSemLinkerNote($strSemLinkerID, $strNoteValue) {
				
				$strSemLinkerID = escapestring($strSemLinkerID);
				$strNoteValue 	= escapestring($strNoteValue);
				
				$this->_DB->queryDB("UPDATE `tbl_sem_linker` SET `note` = '" . $strNoteValue . "' WHERE `sem_linker_idPK` = '" . $strSemLinkerID . "'");
				
			}
			
			public function updateSemLinker($strSemLinkerID) {
					
					$strSemLinkerID = escapestring($strSemLinkerID);
					
					$this->_DB->queryDB("UPDATE `tbl_sem_linker` SET `is_deleted` = 'N' WHERE `sem_linker_idPK` = '" . $strSemLinkerID . "'");
	
			}
			
			public function deleteSemLinker($strSem) {
					
					$strSem 	= escapestring($strSem);
					$this->_DB->queryDB("UPDATE `tbl_sem_linker` SET `is_deleted` = 'Y' WHERE `sem_linker_idPK` = '" . $strSem . "'");
	
			}
			
			public function getSemesterLabel($strSemID) {
				
				$strSemID = escapestring($strSemID);
				
				return $this->_DB->selectDB2("SELECT sl.`course_idFK`, s.`sem_idPK`, s.`sem_year`, s.`sem_sem`, s.`sem_desc`, s.`sem_code`, c.`course_code`
											 FROM
													`tbl_sem_linker` sl
												INNER JOIN `tbl_semesters` s
														ON (sl.`sem_idFK` = s.`sem_idPK`)
												INNER JOIN `tbl_courses` c
														ON (sl.`course_idFK` = c.`course_idPK`)
											 WHERE 		sl.`sem_linker_idPK` = '" . $strSemID . "' LIMIT 1");
	
			}
			
			public function getSemesterLabelValidate($strSemID, $strStudID) {
				
				$strSemID = escapestring($strSemID);
				$strStudID = escapestring($strStudID);
				
				$whereSEmACtive = ( $strSemID > 0 ) ? " `stud_sem_active` = '" . $strSemID . "'" : " `stud_sem_active` != '0'";
				
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count`
											 FROM
													`tbl_stud_subj_linker`
											 WHERE 	" . $whereSEmACtive . " AND `stud_idFK` = '" . $strStudID . "'");
	
			}
			
			public function getSemesterLabelCredits($strStudID) {
				
				$strStudID = escapestring($strStudID);
				
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count`
											 FROM
													`tbl_stud_subj_linker`
											 WHERE 		`stud_sem_active` = '0' AND `stud_idFK` = '" . $strStudID . "'");
	
			}
			
			public function getSemIDNoRecord($course_id, $year_id) {
				
				$course_id = escapestring($course_id);
				$year_id = escapestring($year_id);
				
				return $this->_DB->selectDB2("SELECT `sem_linker_idPK`
											 FROM `tbl_sem_linker`
											 WHERE 	`is_deleted` = 'N' AND `course_idFK` = '" . $course_id . "' AND `year_idFK` = '" . $year_id . "' ORDER BY `sem_linker_idPK` ASC LIMIT 1");
	
			}
			
			public function getCourseHeader($strDeptID) {
				
				$strDeptID = escapestring($strDeptID);
				
				return $this->_DB->selectDB2("SELECT	c.`course_desc`,
														c.`course_code`,
														d.`dept_desc`,
														CONCAT(y.`year_from`, '-', y.`year_to`) as `head_year`,
														h.`head_based`
													FROM `tbl_courses` c
														INNER JOIN `tbl_department` d ON (d.`dept_idPK` = c.`dept_idFK`)
														INNER JOIN `tbl_header` h 	  ON (c.`dept_idFK` = h.`dept_idFK`)
														INNER JOIN `tbl_year` y   	  ON (y.`year_idPK` = h.`year_idFK`)
													WHERE c.`course_idPK` = '" . $strDeptID . "'");
				
			}
			
		###### SEMESTERS
		
			public function getListSemestersDefault() {
				
				return $this->_DB->selectDB("SELECT `sem_idPK`,
													`sem_desc`,
													`sem_year`,
													`sem_sem`,
													`sem_code`
												FROM
													`tbl_semesters`
												WHERE
													`is_deleted` = 'N'");
				
			}
			
		###### SUBJECTS
		
			public function getSubjectListingSemLinker($strSemLinkerID, $activeYear) {
				
				$strSemLinkerID = escapestring($strSemLinkerID);
				$activeYear 	= escapestring($activeYear);
				
				return $this->_DB->selectDB("SELECT `subj_idPK`,
													`subj_desc`,
													`subj_code`,
													`total_units`,
													`lec_units`,
													`lab_units`,
													`lec_lab_units`,
													`co_req`,
													`pre_req`,
													`total_hrs`,
													'' AS `year_idFK`
											 FROM	`tbl_subject`
											 WHERE	`sem_linker_idFK` = '" . $strSemLinkerID . "' AND `is_deleted` = 'N'");
			}
		
			public function getStudentSubjectSemLinkerCurriculum($strStudID, $strCourseID, $strSemLinkerID) {
				
				$strStudID 		= escapestring($strStudID);
				$strCourseID 	= escapestring($strCourseID);
				$strSemLinkerID = escapestring($strSemLinkerID);
				
				return $this->_DB->selectDB("SELECT s.`subj_idPK`,
													s.`subj_desc`,
													s.`subj_code`,
													s.`total_units`,
													s.`lec_units`,
													s.`lab_units`,
													s.`lec_lab_units`,
													s.`co_req`,
													s.`pre_req`,
													s.`total_hrs`,
													tssl.`year_idFK`
											 FROM `tbl_stud_subj_linker` tssl
											 INNER JOIN	`tbl_subject` s ON s.`subj_idPK` = tssl.`subj_idFK`
											 WHERE	tssl.`stud_idFK` = '" . $strStudID . "' AND tssl.`course_idFK` = '" . $strCourseID . "' AND tssl.`sem_idFK` = '" . $strSemLinkerID . "' AND tssl.`is_deleted` = 'N'");
				
			}
		
			public function getStudentSemesterActiveYear($strStudID, $strCourseID, $strSemLinkerID) {
				
				$strStudID 		= escapestring($strStudID);
				$strCourseID 	= escapestring($strCourseID);
				$strSemLinkerID = escapestring($strSemLinkerID);
				
				return $this->_DB->selectDB2("SELECT y.`year_from`, y.`year_to`
											  FROM `tbl_stud_sem` ss INNER JOIN `tbl_year` `y` ON y.`year_idPK` = ss.`year_idFK`
											  WHERE	ss.`stud_idFK` = '" . $strStudID . "' AND ss.`course_idFK` = '" . $strCourseID . "' AND ss.`sem_linker_idFK` = '" . $strSemLinkerID . "' AND ss.`is_deleted` = 'N'");
				
			}
			
			public function getSubject($strSubjID) {
				
				$strSubjID = escapestring($strSubjID);
				
				return $this->_DB->selectDB2("SELECT `subj_idPK`,
													`subj_desc`,
													`subj_code`,
													`total_units`,
													`lec_units`,
													`lab_units`,
													`lec_lab_units`,
													`co_req`,
													`pre_req`,
													`total_hrs`,
													`is_saturday`
											 FROM	`tbl_subject`
											 WHERE	`subj_idPK` = '" . $strSubjID . "' LIMIT 1");
				
			}
			
			public function getSubjectPerCourse($strCourseID) {
				
				$strCourseID = escapestring($strCourseID);
				
				return $this->_DB->selectDB("SELECT s.`subj_idPK`,
													 s.`subj_desc`,
													 s.`subj_code`,
													 sl.`sem_linker_idPK`,
													 sem.`sem_desc`
											 FROM 	`tbl_subject` s
												 INNER JOIN `tbl_sem_linker` sl ON s.`sem_linker_idFK` = sl.`sem_linker_idPK`
												  INNER JOIN `tbl_semesters` sem ON sl.`sem_idFK` = sem.`sem_idPK`
											 WHERE sl.`course_idFK` = '" . $strCourseID . "' AND s.`is_deleted` = 'N' AND sl.`is_deleted` = 'N' ORDER BY sl.`sem_linker_idPK` ASC");
				
			}
			
			public function getSubjectCourse() {
				
				return $this->_DB->selectDB("SELECT s.`subj_idPK`,
													 s.`subj_desc`,
													 s.`subj_code`,
													 sl.`sem_linker_idPK`,
													 sem.`sem_desc`,
													 c.`course_desc`
											 FROM 	`tbl_subject` s
												 INNER JOIN `tbl_sem_linker` sl ON s.`sem_linker_idFK` = sl.`sem_linker_idPK`
												 INNER JOIN `tbl_semesters` sem ON sl.`sem_idFK` = sem.`sem_idPK`
												 INNER JOIN `tbl_courses` c ON c.`course_idPK` = sl.`course_idFK`
											 WHERE s.`is_deleted` = 'N' AND sl.`is_deleted` = 'N' AND c.`is_deleted` = 'N' ORDER BY sl.`sem_linker_idPK` ASC");
				
			}
			
			public function insertSubject($strArray) {
				
				$subjDesc 		= escapestring($strArray['subj_desc']);
				$subjCode 		= escapestring(str_replace(' ', '', $strArray['subj_code']));
				$totalUnits 	= escapestring($strArray['total_units']);
				$lecUnits 		= escapestring($strArray['lec_units']);
				$labUnits 		= escapestring($strArray['lab_units']);
				$lecLabUnits 	= escapestring($strArray['lec_lab_units']);
				$coReq 			= escapestring($strArray['co_req']);
				$preReq 		= escapestring($strArray['pre_req']);
				$totalHrs 		= escapestring($strArray['total_hrs']);
				$semLinkerID 	= escapestring($strArray['sem_linker_id']);
				$yearID 		= escapestring($strArray['year_id']);
				
				$course_id 		= escapestring($strArray['course_id']);
				
				$isSaturday 	= escapestring($strArray['is_saturday']);
				
				$this->_DB->queryDB("INSERT INTO `tbl_subject` (`subj_desc`,
																`subj_code`,
																`total_units`,
																`lec_units`,
																`lab_units`,
																`lec_lab_units`,
																`co_req`,
																`pre_req`,
																`total_hrs`,
																`is_saturday`,
																`sem_linker_idFK`,
																`year_idFK`)
														VALUES ('" . $subjDesc  	. "',
																'" . $subjCode  	. "',
																'" . $totalUnits  	. "',
																'" . $lecUnits  	. "',
																'" . $labUnits  	. "',
																'" . $lecLabUnits	. "',
																'" . $coReq  		. "',
																'" . $preReq  		. "',
																'" . $totalHrs  	. "',
																'" . $isSaturday  	. "',
																'" . $semLinkerID  	. "',
																'" . $yearID  		. "')");
				
				$mysqlLastID = mysql_insert_id();
				
				if( !empty($coReq) ) {
					$coReqExplode = explode('-', $coReq);
					foreach($coReqExplode as $coReqExplodeValue) {
						$coReqExpVal = escapestring($coReqExplodeValue);
						$this->_DB->queryDB("INSERT INTO `tbl_requisite` (`subj_idFK`, `course_idFK`, `sem_linker_idFK`, `co_req`) VALUES ('" . $mysqlLastID . "', '" . $course_id . "', '" . $semLinkerID . "', '" . $coReqExpVal . "')");
						
					}
				}
				
				if( !empty($preReq) ) {
					$preReqExplode = explode('-', $preReq);
					foreach($preReqExplode as $preReqExplodeValue) {
						$preReqExpVal = escapestring($preReqExplodeValue);
						$this->_DB->queryDB("INSERT INTO `tbl_requisite` (`subj_idFK`, `course_idFK`, `sem_linker_idFK`, `pre_req`) VALUES ('" . $mysqlLastID . "', '" . $course_id . "', '" . $semLinkerID . "', '" . $preReqExpVal . "')");
						
					}
				}
				
				return $mysqlLastID;
			
			}
			
			public function getSubjRequisite($strSubjID) {
					
				$strSubjID 	= escapestring($strSubjID);
				
				return $this->_DB->selectDB("SELECT `co_req`, `pre_req` FROM `tbl_requisite` WHERE `subj_idFK` = '" . $strSubjID . "'");
		
			}
			
			public function getSubjCourseRequisite($strCourseID) {
					
				$strCourseID 	= escapestring($strCourseID);
				
				return $this->_DB->selectDB("SELECT `subj_idFK`, `co_req`, `pre_req` FROM `tbl_requisite` WHERE `course_idFK` = '" . $strCourseID . "'");
		
			}
			
			public function updateSubject($strArray, $strSubjID) {
				
				$subjDesc 		= escapestring($strArray['subj_desc']);
				$subjCode 		= escapestring(str_replace(' ', '', $strArray['subj_code']));
				$totalUnits 	= escapestring($strArray['total_units']);
				$lecUnits 		= escapestring($strArray['lec_units']);
				$labUnits 		= escapestring($strArray['lab_units']);
				$lecLabUnits 	= escapestring($strArray['lec_lab_units']);
				$coReq 			= escapestring($strArray['co_req']);
				$preReq 		= escapestring($strArray['pre_req']);
				$totalHrs 		= escapestring($strArray['total_hrs']);
				
				$isSaturday 	= escapestring($strArray['is_saturday']);
				
				$strSubjID 	= escapestring($strSubjID);
				
				$this->_DB->queryDB("UPDATE `tbl_subject` SET `subj_desc` 		= '" . $subjDesc  	. "',
																`subj_code` 	= '" . $subjCode  	. "',
																`total_units` 	= '" . $totalUnits  . "',
																`lec_units` 	= '" . $lecUnits  	. "',
																`lab_units` 	= '" . $labUnits  	. "',
																`lec_lab_units` = '" . $lecLabUnits	. "',
																`co_req` 		= '" . $coReq  		. "',
																`pre_req` 		= '" . $preReq  	. "',
																`is_saturday` 	= '" . $isSaturday  . "',
																`total_hrs` 	= '" . $totalHrs  	. "'
														WHERE `subj_idPK`		= '" . $strSubjID	. "'");
				
				return $strSubjID;
			
			}
			
			public function deleteSubject($strSubjID) {
					
				$strSubjID 	= escapestring($strSubjID);
				
				$this->_DB->queryDB("UPDATE `tbl_subject` SET `is_deleted` = 'Y' WHERE `subj_idPK` = '" . $strSubjID . "'");
			
			}
			
			public function requisite($strSubjLinkerID) {
					
				$strSubjLinkerID 	= escapestring($strSubjLinkerID);
				
				return $this->_DB->selectDB("SELECT `subj_idPK`,
													`subj_desc`,
													`subj_code`,
													`total_units`
											 FROM	`tbl_subject`
											 WHERE	`sem_linker_idFK` = '" . $strSubjLinkerID . "' AND `is_deleted` = 'N'");
		
			}
			
			public function getInputGradeRequisite($strSubjLinkerID, $strSemLinkerActiveID, $strStudID, $strCourseID) {
					
				$strSubjLinkerID 		= escapestring($strSubjLinkerID);
				$strSemLinkerActiveID 	= escapestring($strSemLinkerActiveID);
				$strStudID 				= escapestring($strStudID);
				$strCourseID 			= escapestring($strCourseID);
				
				
				return $this->_DB->selectDB("SELECT DISTINCT * FROM (SELECT	 s.`subj_idPK`,
																		  s.`subj_desc`,
																		  s.`subj_code`,
																		  s.`total_units`,
																		  s.`sem_linker_idFK`,
																		  s.`pre_req`,
																		  '' AS `grade`
																		FROM `tbl_subject` s
																		WHERE s.`sem_linker_idFK` IN (" . $strSubjLinkerID . ")
																			AND s.`is_deleted` = 'N'
																UNION
																SELECT	s.`subj_idPK`,
																		 s.`subj_desc`,
																		 s.`subj_code`,
																		 s.`total_units`,
																		 s.`sem_linker_idFK`,
																		 s.`pre_req`,
																		 tssl.`grade`
																	   FROM `tbl_stud_subj_linker` tssl
																		 INNER JOIN `tbl_subject` s
																		   ON tssl.`subj_idFK` = s.subj_idPK
																	   WHERE tssl.`grade` IN('DRP', 'INC', '4.0', '4', '5', '5.0')
																		   AND tssl.`stud_sem_active` = '" . $strSemLinkerActiveID . "'
																		   AND tssl.`stud_idFK` = '" . $strStudID . "'
																		   AND tssl.`course_idFK` = '" . $strCourseID . "'
																		   
												) AS `tbl` ORDER BY `sem_linker_idFK`, `subj_idPK`");
		
			}
			
			public function getStudentNextSubjectDetails($strSubjLinkerID) {
					
				$strSubjLinkerID = escapestring($strSubjLinkerID);
				
				return $this->_DB->selectDB("SELECT s.`subj_idPK`,
													 s.`subj_desc`,
													 s.`subj_code`,
													 s.`total_units`,
													 s.`sem_linker_idFK`,
													 s.`pre_req`,
													 '' AS `grade`
													FROM `tbl_subject` s
													WHERE s.`sem_linker_idFK` = " . $strSubjLinkerID . "
														AND s.`is_deleted` = 'N' ORDER BY s.`sem_linker_idFK` DESC LIMIT 1");
		
			}
		
			public function getSemesterLinkerFouthYear($strYear, $strActiveSem) {
				
				$strYear 	= escapestring($strYear);
				$strActiveSem 	= escapestring($strActiveSem);
				
				return $this->_DB->selectDB2("SELECT sl.`sem_linker_idPK` FROM `tbl_semesters` s INNER JOIN `tbl_sem_linker` sl ON s.`sem_idPK` = sl.`sem_idFK` WHERE s.`sem_year` = '" . $strYear . "' AND sl.`sem_linker_idPK` NOT IN (" . $strActiveSem . ")");
		
				
			}
			
			public function getRequisiteSubject($strSubjRequisite, $strCourse) {
					
				if ( !empty($strSubjRequisite) ) {
					$strSubjReq = "";
					foreach($strSubjRequisite as $strSubjRequisiteValue) {
						$strSubjReq .= "'" . escapestring($strSubjRequisiteValue) . "',";
					}
					$strSubjReqRes = trim(rtrim($strSubjReq, ','));
					
					if ( !empty($strCourse) ) {
						$whereCourse = " AND sl.`course_idFK` = '" . $strCourse . "'";
					}
					
					$result = $this->_DB->selectDB("SELECT s.`subj_idPK`
													FROM	`tbl_subject` s
													INNER JOIN `tbl_sem_linker` sl ON s.`sem_linker_idFK` = sl.`sem_linker_idPK`
													WHERE	s.`subj_code` IN (" . $strSubjReqRes . ")
													AND s.`is_deleted` = 'N'" . $whereCourse);
					return $result;
				}
			}
			
			public function getReqSubjectID($strSubjRequisite) {
					
				if ( !empty($strSubjRequisite) ) {
					$strSubjReq = "";
					foreach($strSubjRequisite as $strSubjRequisiteValue) {
						$strSubjReq .= "'" . escapestring($strSubjRequisiteValue) . "',";
					}
					$strSubjReqRes = trim(rtrim($strSubjReq, ','));
					
					return $this->_DB->selectDB("SELECT `subj_code`
												 FROM	`tbl_subject`
												 WHERE	`subj_idPK` IN (" . $strSubjReqRes . ")");
				}
			}
			
			public function getFees() {
				return $this->_DB->selectDB("SELECT `fee_idPK`, `fee_desc`
											 FROM	`tbl_fees`
											 WHERE	`is_deleted` = 'N'");
			}
		
			public function getSemFeesLinker($strSFLID) {
				
				$strSFLID 	= escapestring($strSFLID);
				
				return $this->_DB->queryDB("SELECT `sem_fees_linker_idPK`, `sem_linker_idFK`, `fee_idFK`, `amount` FROM `tbl_sem_fees_linker` WHERE `sem_fees_linker_idPK`	= '" . $strSFLID	. "' AND `is_deleted` = 'N'");
				
			}
			
			public function deleteSemesterFeesActive($strSemFeesLinkerID) {
				
				$strSemFeesLinkerID 	= escapestring($strSemFeesLinkerID);
				
				$this->_DB->queryDB("UPDATE `tbl_sem_fees_linker` SET `is_deleted` = 'Y' WHERE `sem_fees_linker_idPK` = '" . $strSemFeesLinkerID . "'");
			
			}
			
			public function getSemFeesLinkerListing($strSLID) {
				
				$strSLID 	= escapestring($strSLID);
				
				return $this->_DB->selectDB("SELECT DISTINCT sfl.`sem_fees_linker_idPK`,
															 sfl.`sem_linker_idFK`,
															 sfl.`fee_idFK`,
															 f.`fee_desc`,
															 sfl.`amount`
											FROM 	`tbl_sem_fees_linker` sfl
											INNER JOIN `tbl_fees` f ON sfl.`fee_idFK` = f.`fee_idPK`
											WHERE 	`sem_linker_idFK`	= '" . $strSLID	. "' AND sfl.`is_deleted` = 'N'");
				
				
			}
			
			public function getSemFeesLinkerListingActive($strSLID) {
				
				$strSLID 	= escapestring($strSLID);
				
				return $this->_DB->selectDB("SELECT DISTINCT sfl.`sem_fees_linker_idPK`,
															 sfl.`sem_linker_idFK`,
															 sfl.`fee_idFK`,
															 f.`fee_desc`,
															 sfl.`amount`
											FROM 	`tbl_sem_fees_linker` sfl
											INNER JOIN `tbl_fees` f ON sfl.`fee_idFK` = f.`fee_idPK`
											WHERE 	`sem_linker_idFK`	= '" . $strSLID	. "' AND sfl.`is_deleted` = 'N'");
				
				
			}
			
			public function getTotalUnitsPerSemester($strSLID) {
				
				$strSLID 	= escapestring($strSLID);
				
				return $this->_DB->selectDB2("SELECT SUM(ABS(s.`total_units`)) as total_units FROM `tbl_subject` s WHERE s.sem_linker_idFK = '" . $strSLID . "' AND `is_deleted` = 'N'");
				
				
			}
		
			public function insertSemFeesLinker($strSLID, $strFee, $strAmount) {
				
				$strSLID 	= escapestring($strSLID);
				$strFee 	= escapestring($strFee);
				$strAmount 	= escapestring($strAmount);
				
				$this->_DB->queryDB("INSERT INTO `tbl_sem_fees_linker` (`sem_linker_idFK`, `fee_idFK`, `amount`) VALUES ('" . $strSLID . "', '" . $strFee . "', '" . $strAmount . "')");
				
			}
			
			public function updateSemFeesLinker($strSLID, $strFee, $strAmount, $strSFLID) {
					
				$strSLID 	= escapestring($strSLID);
				$strFee 	= escapestring($strFee);
				$strAmount 	= escapestring($strAmount);
				$strSFLID 	= escapestring($strSFLID);
				
				$this->_DB->queryDB("UPDATE `tbl_sem_fees_linker`
									 SET `amount` 	= '" . $strAmount   . "'
									 WHERE `sem_fees_linker_idPK`	= '" . $strSFLID	. "'");
				
			}
			
			public function getSemLinkerFeeSemesterLinker($strSFLID) {
				
				$strSFLID 	= escapestring($strSFLID);
				
				return $this->_DB->selectDB2("SELECT sfl.`sem_fees_linker_idPK`,
													sfl.`sem_linker_idFK`,
													sfl.`fee_idFK`,
													sfl.`amount`
											FROM 	`tbl_sem_fees_linker` sfl
											WHERE 	sfl.`sem_fees_linker_idPK`	= '" . $strSFLID	. "' AND `is_deleted` = 'N'");
				
			}
		
			public function insertStudentPaymentHistory($strActiveSem, $strStudID, $strCourseID, $strAmount, $strPaymentMethod) {
		
				$strStudID 			= escapestring($strStudID);
				$strActiveSem 		= escapestring($strActiveSem);
				$strCourseID		= escapestring($strCourseID);
				$strAmount			= escapestring($strAmount);
				$strPaymentMethod	= escapestring($strPaymentMethod);
				
				$this->_DB->queryDB("INSERT INTO `tbl_stud_subj_fees` (`stud_idFK`, `sem_linker_idFK`, `course_idFK`, `amount`, `payment_mode`) VALUES ('" . $strStudID . "', '" . $strActiveSem . "', '" . $strCourseID . "', '" . $strAmount . "', '" . $strPaymentMethod . "')");
				
			}
		
			public function getStudentPaymentHistory($strActiveSem, $strStudID, $strCourseID) {
		
				$strActiveSem 	= escapestring($strActiveSem);
				$strStudID 		= escapestring($strStudID);
				$strCourseID	= escapestring($strCourseID);
				
				return $this->_DB->selectDB("SELECT ssf.`amount`,
													ssf.`payment_mode`,
													ssf.`timestamp`
											FROM 	`tbl_stud_subj_fees` ssf
											WHERE 	ssf.`stud_idFK`			= '" . $strStudID		. "'
												AND ssf.`sem_linker_idFK`	= '" . $strActiveSem	. "'
												AND ssf.`course_idFK`		= '" . $strCourseID		. "' AND ssf.`is_deleted` = 'N'");
				
			}
		
			public function getSeectionList($strCourseID) {
				
				$strCourseID = escapestring($strCourseID);
				
				return $this->_DB->selectDB("SELECT `section_idPK`, `section_desc`, `section_code`, `course_idFK`, `no_stud` WHERE `course_idFK`	= '" . $strCourseID	. "' AND `is_deleted` = 'N'");
				
			}
			public function getProfSubjectList($strProf, $strSubjID, $strEqualSubjID) {
				
				$strProf	= escapestring($strProf);
				$strSubjID 	= escapestring($strSubjID);
				
				if ( !empty($strSubjID) ) {
					$whereSubj = " AND ps.`subj_idFK` = '" . $strSubjID . "'";
				}
				
				$whereProf = ( $strEqualSubjID === true ) ? " ps.`user_idFK` = '" . $strProf . "'" : " ps.`user_idFK` != '" . $strProf . "'";
				
				return $this->_DB->selectDB("SELECT DISTINCT ps.`prof_subj_idPK`,
															ps.`subj_idFK`,
															ua.`last_name`,
															ua.`first_name`,
															s.`subj_desc`,
															s.`subj_code`,
															s.`total_units`,
															s.`total_hrs`,
															s.`sem_linker_idFK`,
															c.`course_code`
													FROM `tbl_prof_subj` ps
													INNER JOIN `tbl_subject` s
														ON (ps.`subj_idFK` = s.`subj_idPK`)
													INNER JOIN `tbl_sem_linker` sl
														ON (s.`sem_linker_idFK` = sl.`sem_linker_idPK`)
													INNER JOIN `tbl_courses` c
														ON (sl.`course_idFK` = c.`course_idPK`)
													INNER JOIN `tbl_users_account` ua
														ON (ps.`user_idFK` = ua.`user_idPK`)
													WHERE " . $whereProf . " AND ps.`is_deleted` = 'N'" . $whereSubj);
				
			}
		
			public function insertProfSubj($strSubj, $strProf, $strYear) {
				
				$strSubj = escapestring($strSubj);
				$strProf = escapestring($strProf);
				$strYear = escapestring($strYear);
				
				$this->_DB->queryDB("INSERT INTO `tbl_prof_subj` (`user_idFK`, `subj_idFK`, `year_idFK`) VALUES ('" . $strProf . "', '" . $strSubj . "', '" . $strYear . "')");
				
			}
		
			public function getRoomsDepartment($strDept) {
				
				$strDept = escapestring($strDept);
				
				return $this->_DB->selectDB("SELECT `room_idPK`, `room_type`, `room_desc`, `dept_idFK` FROM `tbl_rooms` WHERE `dept_idFK` = '" . $strDept . "' AND `is_deleted` = 'N' ORDER BY `room_idPK` DESC");
				
			}
		
		
			public function insertDeptRooms($strArray) {
				
				$strRooms  = escapestring($strArray['room']);
				$strLabLec = escapestring($strArray['lab_lec']);
				$strDept   = escapestring($strArray['dept_id']);
				
				$this->_DB->queryDB("INSERT INTO `tbl_rooms` (`room_desc`, `room_type`, `dept_idFK`) VALUES ('" . $strRooms . "', '" . $strLabLec . "', '" . $strDept . "')");
				
			}
		
			public function updateDeptRooms($strRooms) {
				
				$strRooms = escapestring($strRooms);
				
				$this->_DB->queryDB("UPDATE `tbl_rooms` SET `is_deleted` = 'Y' WHERE `room_idPK` = '" . $strRooms . "'");
				
			}
		
			public function updateProfSubj($strSubj, $strProf, $strYear) {
				
				$strSubj = escapestring($strSubj);
				$strProf = escapestring($strProf);
				$strYear = escapestring($strYear);
				
				$this->_DB->queryDB("UPDATE `tbl_prof_subj` SET `is_deleted` = 'Y' WHERE `user_idFK` = '" . $strProf . "' AND `subj_idFK` = '" . $strSubj . "' AND `year_idFK` = '" . $strYear . "'");
				
			}
		
			public function getStudentSemesteryearCourse($strStudID, $strCourseID) {
				
				$strStudID		= escapestring($strStudID);
				$strCourseID 	= escapestring($strCourseID);
				
				return $this->_DB->selectDB("SELECT DISTINCT tssl.`stud_sem_active`, sem.`sem_idPK`, sem.`sem_code`, sem.`sem_desc`
													FROM 	`tbl_stud_subj_linker` tssl
													INNER JOIN `tbl_sem_linker` sl
														ON (tssl.`stud_sem_active` = sl.`sem_linker_idPK`)
													INNER JOIN `tbl_semesters` sem
														ON (sl.`sem_idFK` = sem.`sem_idPK`)
													WHERE 	 tssl.`stud_idFK` = '" . $strStudID 	. "'
														AND  tssl.course_idFK = '" . $strCourseID 	. "'
													ORDER BY sem.`sem_idPK` ASC");
			}
			
			public function getSemesterDescViaLinker($strSemLinkerID) {
				
				$strSemLinkerID	= escapestring($strSemLinkerID);
				
				return $this->_DB->selectDB2("SELECT s.`sem_desc`, s.`sem_code` FROM `tbl_sem_linker` sl INNER JOIN `tbl_semesters` s ON (sl.`sem_idFK` = s.`sem_idPK`) WHERE sl.`sem_linker_idPK` = '" . $strSemLinkerID . "'");
			}
		
			public function inputGradeRequisiteGradeIfDrop($strSubjID, $strSemLinkerID, $studID) {
				
				$strSubjID		= escapestring($strSubjID);
				$strSemLinkerID	= escapestring($strSemLinkerID);
				$studID			= escapestring($studID);
 
				return $this->_DB->selectDB2("SELECT `grade` FROM `tbl_stud_subj_linker` WHERE `subj_idFK` = '" . $strSubjID . "' AND `stud_sem_active` = '" . $strSemLinkerID . "' AND `stud_idFK` = '" . $studID . "'");
				
			}
		
			public function getSemesterSY() {
 
				return $this->_DB->selectDB("SELECT `year_idPK`, `year_from`, `year_to`, `is_summer` FROM `tbl_year` ORDER BY `year_idPK` DESC");
				
			}
		
			public function getNextSummerSY() {
				return $this->_DB->selectDB2("SELECT COUNT(*) AS `count` FROM `tbl_sem_linker` WHERE `sem_idFK` IN ( SELECT `sem_idPK` FROM `tbl_semesters` WHERE `sem_sem` = 0 ) AND `is_deleted` = 'N'");
			}
		
	}
?>